How to create Tables, Color with Conditional formatting, and add Drill through Functionality 12
Follow the Steps below to build Ticket Details Table
Duplicate the FIrst Page and Remove the Four Chart visuals
1. Drag and drop a table from visualizations.
Dimensions:
Ticket Details Title
General
x-434,Y-181, W-300, H-62
Table
General x-0,
Y-244, W-1279, H-556
2. Drag and drop the following columns in Values from Zendesk Table
·
ForTheWeek
·
CreatedDate
·
CreatedTime
·
AgnetName
·
TicketLink
·
HoursCatagory
·
FirstRspHrs
·
1stconRate
·
DaysOpen and
· Status
3. Column headers
·
Font color – White
·
Background color - #005AA1
·
Outline – Bottom only
·
Font family – Segoe UI
·
Text size - 10pt
·
Alignment: Center
4. Values
·
Font color – Black
·
Background color – White
·
Alternate font color Black
·
Alternate Background color – white
·
URL icon – on
·
Font family – Segoe UI
·
Text size - 10pt
5. Field Formatting
·
For all columns
Font color – white
Background color –
white
Alignment - Center
6- Conditional Formatting
·
Set Background Color = on and use the following DAX Query for
color formatting
Create Table by going to the Home Ribbon and Enter Data
Format
Right click on Hour Category
Conditional Formatting
Background
Create
the calculated column in the main table
Sort Color = RELATED('Sort
Hour & Color'[Color])
1st Contact RRate Conditional Formatting
· Hide the 1st ConRRate Values and leave only the background color
Go to the Format
Conditional Formatting
Select the 1st ConRRate from Drop
down
Font color On
Advanced control
Set up the Rules and make it Summarization Median
FirstRespHrs
After You
create Ticket Details table drag and drop the columns from Zendesk ticket
system table to Drill Through Filed box.
Create a drill through table for Open Tickets
Steps below to build this report
1. Drag and drop a table from visualizations.
2. Drag and drop
·
AgnetName
·
CreatedDate
·
TicketID
·
TicketLink
·
OpenTickets
·
DaysOpen
·
Status
·
DaysSinceLastComment
3. Column headers
·
Font color – White
·
Background color - #B4009E
·
Outline – Bottom only
·
Font family – Segoe UI
·
Text size - 10pt
·
Alignment: Center
4. Values
·
Font color – Black
·
Background color – White
·
Alternate font color Black
·
Alternate Background color – white
·
URL icon – on
·
Font family – Segoe UI
·
Text size - 10pt
5. Totals
·
Totals - on
·
Font color – Black
·
Background color – #1F6DAB
·
Outline – Top only
·
Font Family – Comic Sans MS
·
Text size – 12pt
6. Field Formatting
·
For all columns
Font color – white
Background color – white
Alignment - Auto
7. Font color Formatting for ‘Status’
column
Create a new table and build a relationship in the model view for color coding as follows:
Data Model >>>>>>>>Select Status >>>Sort Column by Color
Created Calculated column as below to use it in sorting in main table
Status Color = RELATED('Status Sort'[Color])
How to make drillthrough in PowerBI reports